/**
 * 
 */
package model;

import java.awt.Point;
import java.beans.XMLDecoder;
import database.*;
import java.beans.XMLEncoder;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Observer;
import java.util.Map.Entry;
import java.util.Observable;

import database.Database;
import database.Field;
import database.Table;
import java.util.regex.*;

import javax.swing.text.html.HTMLDocument.Iterator;

/**
 * @author Ken
 *
 */
public class Model extends Observable{
	private List<Database> _databases;
	private List<Observer> _obObservers;	
	
	@Override
	public synchronized void addObserver(Observer o) {
		_obObservers.add(o);
		super.addObserver(o);
	}
	
	public Model() {
		_databases = new ArrayList<Database>();
		_obObservers = new ArrayList<Observer>();
	}
	public Model(List<Database> databases) {
		_databases = databases;
		_obObservers = new ArrayList<Observer>();
	}
	/**
	 * @return the _databases
	 */
	public List<Database> get_databases() {
		return _databases;
	}
	/**
	 * @param _databases the _databases to set
	 */
	public void set_databases(List<Database> _databases) {
		this._databases = _databases;
	}	
	public String createDatabase(String name) {		 
		for (int i = 0; i < _databases.size(); i++) {
			if (_databases.get(i).get_name().equals(name))			
				return "Database nay da ton tai";							
		}
		Database db = new Database();
		db.set_name(name);
		_databases.add(db);
		return "Success";		
	}
	public Database getDatabase(String name) {
		for (int i = 0; i < _databases.size(); i++) {
			if (_databases.get(i).get_name().equals(name))
				return _databases.get(i);
		}
		return null;
	}
	public Table getTable(String dbName, String tbName) {
		Database db = getDatabase(dbName);
		if (db == null)
			return null;
		return db.getTable(tbName);
	}	
	public String removeDatabase(String name) {
		Database db = getDatabase(name);
		if (db != null)
		{
			_databases.remove(db);
			return "Success";
		}
		return "Khong tim thay Database trong CSDL";		
	}
	public String createTable(String dbName, String tbName) {
		Database db = getDatabase(dbName);
		if (db == null)
			return "Khong tim thay Database trong CSDL";		
		boolean result = db.addTable(tbName);
		if (!result)
			return "Trong CSDL da ton tai Table nay";
		return "Success";
	}	
	public String saveDatabase(String name) {
		Database db = getDatabase(name);
		if (db == null)
			return "Khong tim thay database";
		try {			
			if (checkTrigger(db)) {
				XMLEncoder encoder = new XMLEncoder(new BufferedOutputStream(new FileOutputStream("data/" + db.toString() + ".xml")));
				encoder.writeObject(db);
				encoder.close();
				return "Success";
			}							
			return "Fail";
		}
		catch (Exception e) {
			return e.toString();
		}
	}
	private boolean checkTrigger(Database db) {
		List<Table> tables = db.get_tables();
		for (int i = 0; i < tables.size(); i++) {
			if (!checkTriggerTable(tables.get(i)))
				return false;
		}			
		return true;
	}

	private boolean checkTriggerTable(Table table) {
		List<Field> fields = table.get_fields();
		List<Row> rows = table.get_rows();
		int[] primary = new int[fields.size()];
		int index = 0;
		for (int i = 0; i < fields.size(); i++) {
			Field f = fields.get(i);
			if (f.is_isPrimary())
				primary[index++] = i;
		}
		
		for (int i = 0; i < rows.size(); i++) {
			Row r1 = rows.get(i);
			List<Object> r1Datas = r1.get_data();
			
			//Kiem tra nhap kieu du lieu dung ko
			for (int j = 0; j < fields.size(); j++) {
				String type = fields.get(j).get_dataType();
				if (type.equalsIgnoreCase("int")) {
					Integer inte = null;
					try {
						inte = Integer.parseInt(r1Datas.get(j).toString()); 
					}
					catch (Exception e) {
						return false;
					}
				}
			}
				
			List<String> pri1 = new ArrayList<String>();
			for (int j = 0; j < index; j++) {
				pri1.add(r1Datas.get(primary[j]).toString());
			}
			for (int j = i + 1; j < rows.size(); j++) {
				Row r2 = rows.get(j);
				List<Object> r2Datas = r2.get_data();
				List<String> pri2 = new ArrayList<String>();
				for (int h = 0; h < index; h++) {
					pri2.add(r2Datas.get(primary[h]).toString());
				}
				for (int k = 0; k < pri1.size(); k++) {
					if (pri1.get(k).equals(pri2.get(k)))
						return false;
				}
			}
		}
		
		return true;
	}

	public void loadDatabase() {
		try {
			_databases.clear();
			File f = new File(".");
			String dir = f.getCanonicalPath();
			dir += "/data";
			File folder = new File(dir);
			File[] files = folder.listFiles();
			for (File file : files) {
				XMLDecoder decoder = new XMLDecoder(new BufferedInputStream(new FileInputStream(file)));
				Database db = (Database)decoder.readObject();
				this._databases.add(db);
			}
		}
		catch (Exception e) {			
		}
	}
	public String saveDatabase(String dbName, String tName,
			List<List<String>> tableProperty, List<List<String>> tableData) {
		Table t = getTable(dbName, tName);
		if (t == null)
			return "Khong tim thay Table";	
		List<Field> fields = t.get_fields();
		fields.clear();		
		if (tableProperty != null) {			
			//TODO: Kiem tra cac trigger o day neu thay can thiet
			for (int i = 0; i < tableProperty.size(); i++) {
				List<String> s = tableProperty.get(i);
				Field f = new Field(s.get(1), s.get(2));
				f.set_isPrimary(new Boolean(s.get(0)));
				fields.add(f);
			}
			t.set_fields(fields);
		}				
		if (tableData != null){
			List<database.Row> rows = t.get_rows();
			rows.clear();
			for (int i = 0; i < tableData.size(); i++) {
				List<String> s = tableData.get(i);
				database.Row r = new database.Row(); 
				for (int j = 0; j < fields.size(); j++) {
					if (j >= s.size())
						r.add("");
					else											
						r.add(s.get(j));
				}
				rows.add(r);
			}
		}
		saveDatabase(dbName);
		return "Success";
	}
	
	enum QUERY {
        INSERT,
        UPDATE,
        DELETE,
        SELECT
    }
	
	@Override
	public void notifyObservers(Object arg) {
		for (int i = 0; i < _obObservers.size(); i++) {
			_obObservers.get(i).update(this, arg);
		}
		super.notifyObservers(arg);
	}
	
	public Object executeQuery(String query, String database) {
        String normalize = query.toUpperCase();
        int beginIndex = 0;
        int endIndex = 0;
        try {
            endIndex = normalize.indexOf("INSERT");
            if (endIndex >= beginIndex)
            {
            	executeInsertQuery(query, database);
            	loadDatabase();             	
            	notifyObservers(null);
            	return null;
            }
            endIndex = normalize.indexOf("DELETE");
            if (endIndex >= beginIndex)
            {
            	executeDeleteQuery(query, database);
            	loadDatabase();
            	notifyObservers(null);
            	return null;
            }
            endIndex = normalize.indexOf("SELECT");
            if (endIndex >= beginIndex) {
            	return executeSelectQuery(query, database);
            }
            	
            endIndex = normalize.indexOf("UPDATE");
            if (endIndex >= beginIndex)
            {
            	executeUpdateQuery(query, database);
            	loadDatabase();
            	notifyObservers(null);
            	return null;
            }                
        } catch (Exception e) {
            
        }
        
        return null;
    }	
	
	private Table joinTable(Table t1, String alias1, Table t2, String alias2) {
		if (t1 == null) {
			List<Field> f2 = t2.get_fields();
			List<Field> result = new ArrayList<Field>();			
			for (int i = 0; i < f2.size(); i++) {
				result.add(new Field(alias2 != null ? alias2 + "." + f2.get(i).get_name() : f2.get(i).get_name(), f2.get(i).get_dataType()));
			}
			List<Row> r2 = t2.get_rows();
			List<Row> rows = new ArrayList<Row>();
			for (int i = 0; i < r2.size(); i++) {
				Row r = new Row();
				for (int j = 0; j < result.size(); j++) {
					r.add(r2.get(i).get_data().get(j));	
				}				
				rows.add(r);
			}
			Table re = new Table("temp", result, rows);
			return re;
		}
		else {							
			List<Field> f1 = t1.get_fields();
			List<Field> f2 = t2.get_fields();
			List<Field> result = new ArrayList<Field>();
			for (int i = 0; i < f1.size(); i++) {
				result.add(new Field(alias1 != null ? alias1 + "." + f1.get(i).get_name() : f1.get(i).get_name(), f1.get(i).get_dataType()));
			}
			for (int i = 0; i < f2.size(); i++) {
				result.add(new Field(alias2 != null ? alias2 + "." + f2.get(i).get_name() : f2.get(i).get_name(), f2.get(i).get_dataType()));
			}
		
			List<Row> rows = new ArrayList<Row>();
			List<Row> r1 = t1.get_rows();
			List<Row> r2 = t2.get_rows();
			for (int i = 0; i < r1.size(); i++) {
				for (int j = 0; j < r2.size(); j++) {
					Row r = new Row();				
					for (int h = 0; h < f1.size(); h++) {
						r.add(r1.get(i).get_data().get(h));
					}
					for (int h = 0; h < f2.size(); h++) {
						r.add(r2.get(j).get_data().get(h));
					}
					rows.add(r);
				}
			}
			Table re = new Table("temp", result, rows);
			return re;
		}		
	}
	
	private List<List<String>> executeWhere(String selectString, String fromString, String whereString, String database, List<Field> f) {
		int i = 0;
		FromQuery fromQuery = new FromQuery(fromString);
		FromQueryData fqd = fromQuery.queryParse();
        List<PairValue> pairs = fqd.getPairValues();            
        Table result = null;//getTable(database, pairs.get(0).getKey());
        for (i = 0; i < pairs.size(); i++) {
			PairValue pv = pairs.get(i);
			String tableName = pv.getKey();
			Table t = getTable(database, tableName);
			if (t == null)
				return null;
			String alias = i == 0 ? pairs.get(0).isIsAlias() ? pairs.get(0).getValue() : pairs.get(0).getKey() : null;				
			result = joinTable(result, alias, t, pv.isIsAlias() ? pv.getValue() : pairs.size() > 1 ? t.get_name() : null);
		}// Qua trinh join bang ket thuc
                                
        List<Field> fields = new ArrayList<Field>();//Tao ra truong field gia
        for (i = 0; i < result.get_fields().size(); i++) {
			fields.add(new Field(result.get_fields().get(i).get_name(), result.get_fields().get(i).get_dataType()));
		}
        for (int j = 0; j < fields.size(); j++) {
			f.add(fields.get(j));
		}        
        List<Row> rows = result.get_rows();               
        
        int[]flag = new int[rows.size()];
        if (fqd.getFromQueryType() == FromQueryType.JOIN) {
        	String cond = "Where " + fqd.getCondition();
        	for (int j = 0; j < rows.size(); j++) {
        		boolean bool = ParseWhere.QueryParse(fields, rows.get(i), whereString);
        		if (!bool)
        			flag[i] = 1;
			}        	
        }
                                               
        for (i = 0; i < rows.size(); i++) {						
        	boolean bool = ParseWhere.QueryParse(fields, rows.get(i), whereString);
			if (!bool)
				flag[i] = 1;				
		}
                             
        for (i = flag.length - 1; i > -1; i--) {
			if (flag[i] == 1)
				rows.remove(i);
		}
        
        selectString = selectString.trim();
        List<List<String>> ret = new ArrayList<List<String>>();
        
        	for (i = 0; i < rows.size(); i++) {
				List<String> arr = new ArrayList<String>();
				for (int j = 0; j < fields.size(); j++) {
					arr.add(rows.get(i).get_data().get(j).toString());	
				}				
				ret.add(arr);
			}            
        
        return ret;
	}
	
	private Object executeSelectQuery(String root, String database) {
		
		try {
			String query = root.toLowerCase();
			
            int beginIndex = 0;
            int endIndex = 0;
            
            String selectString;
            String fromString;
            String whereString;         
            String tableName;            
            
            List<Field> field = new ArrayList<Field>();
            
            endIndex = query.indexOf("select", beginIndex);
            if (endIndex < beginIndex) return null;
            beginIndex = endIndex + 6;
            endIndex = query.indexOf("from", beginIndex);
            if (endIndex < beginIndex) return null;                                                
            selectString = root.substring(beginIndex, endIndex); //select fields
                        
            beginIndex = endIndex;
            endIndex = query.indexOf("where", beginIndex);
            List<List<String>> ret = new ArrayList<List<String>>();
            if (endIndex < beginIndex) {// Khong co where
            	endIndex = query.indexOf("group by", beginIndex);
            	if (endIndex < beginIndex) {
            		endIndex = query.indexOf("having", beginIndex);
            		if (endIndex < beginIndex) {
            			endIndex = query.indexOf("order by", beginIndex);
            			if (endIndex < beginIndex) {
            				fromString = root.substring(beginIndex);
            			}
            			else {
            				fromString = root.substring(beginIndex, endIndex);
            			}
            		}
            		else {
            			fromString = root.substring(beginIndex, endIndex);
            		}
            	}
            	else {
            		fromString = root.substring(beginIndex, endIndex);
            	}
            	FromQuery fromQuery = new FromQuery(fromString);
            	FromQueryData fqd = fromQuery.queryParse();
            	List<PairValue> pairs = fqd.getPairValues();            
                Table result = null;//getTable(database, pairs.get(0).getKey());
                for (int i = 0; i < pairs.size(); i++) {
        			PairValue pv = pairs.get(i);
        			tableName = pv.getKey();
        			Table t = getTable(database, tableName);
        			if (t == null)
        				return null;
        			String alias = i == 0 ? pairs.get(0).isIsAlias() ? pairs.get(0).getValue() : pairs.get(0).getKey() : null;				
        			result = joinTable(result, alias, t, pv.isIsAlias() ? pv.getValue() : pairs.size() > 1 ? t.get_name() : null);
        		}// Qua trinh join bang ket thuc
                for (int i = 0; i < result.get_fields().size(); i++) {
					field.add(result.get_fields().get(i));
				}                
                List<Row> rows = result.get_rows();
                
                if (fqd.getFromQueryType() == FromQueryType.JOIN) {
                	int[]flag = new int[rows.size()];
                	String cond = "where " + fqd.getCondition().trim();
                	for (int i = 0; i < rows.size(); i++) {
						boolean b = ParseWhere.QueryParse(field, rows.get(i), cond);
						if (!b)
							flag[i] = 1;
					}
                	for (int i = flag.length - 1; i > -1; i--) {
            			if (flag[i] == 1)
            				rows.remove(i);
            		}
                }
                                               
                for (int i = 0; i < rows.size(); i++) {
					Row r = rows.get(i);
					List<Object> objs = r.get_data();
					List<String> data = new ArrayList<String>();
					for (int j = 0; j < objs.size(); j++) {
						data.add(objs.get(j).toString());
					}
					ret.add(data);
				}
            }
            else {// Co Where
            	fromString = root.substring(beginIndex, endIndex);
            	beginIndex = beginIndex + fromString.length();
            	tableName = fromString.trim();
            	endIndex = query.indexOf("group by", beginIndex);
            	if (endIndex < beginIndex) {
            		endIndex = query.indexOf("having", beginIndex);
            		if (endIndex < beginIndex) {
            			endIndex = query.indexOf("order by", beginIndex);
            			if (endIndex < beginIndex) {
            				whereString = root.substring(beginIndex);
            			}
            			else {
            				whereString = root.substring(beginIndex, endIndex);
            			}
            		}
            		else {
            			whereString = root.substring(beginIndex, endIndex);
            		}
            	}
            	else {
            		whereString = root.substring(beginIndex, endIndex);
            	}                  	
                ret = executeWhere(selectString, fromString, whereString, database, field);
            }
//          Xu ly group by o day
//          Xu ly having o day
            
            SelectQuery sq = new SelectQuery(selectString);
            List<Row> rows = new ArrayList<Row>();
            for (int i = 0; i < ret.size(); i++) {
            	List<String> strs = ret.get(i);
            	Row r = new Row();
				for (int j = 0; j < strs.size(); j++) {
					r.add(strs.get(j));
				}
				rows.add(r);
			}
            List<SelectQueryData> sqds = sq.queryParse(rows, field);
            if (sqds.size() > 1) {
            	List<Integer> removeFieldIndex = new ArrayList<Integer>();
                for (int i = 0; i < sqds.size(); i++) {
    				SelectQueryData sqd = sqds.get(i);
    				String columnName = sqd.getColumnNames().get(0).trim();
    				int index = 0;    				
    				while (index < field.size()) {
    					Field f = field.get(index);
						String str = f.get_name();
						if (columnName.equalsIgnoreCase(str)) {
							removeFieldIndex.add(index);
							break;
						}							
    					index++;
    				}    				    	
    			}
                for (int i = field.size() - 1; i >= 0; i--) {
                	if (!removeFieldIndex.contains(new Integer(i)))
                		field.remove(i);					
				}
                for (int i = 0; i < rows.size(); i++) {
					Row r = rows.get(i);
					List<Object> data = r.get_data();
					for (int j = data.size() - 1; j >= 0; j--) {
						if (!removeFieldIndex.contains(new Integer(j)))
							data.remove(j);
					}
				}
            }
            else {//Chi co 1 truong duy nhat
            	SelectQueryData sqd = sqds.get(0);
            	if (sqd.getSelectQueryType() == SelectQueryType.ALL) {
            		
            	}
            	else {
            		if (sqd.getSelectQueryType() == SelectQueryType.DISTINCT) {
                		List<String> columnNames = sqd.getColumnNames();
                		List<Integer> removeFieldIndex = new ArrayList<Integer>();
                        for (int i = 0; i < columnNames.size(); i++) {            				
            				String columnName = columnNames.get(i).trim();
            				int index = 0;    				
            				while (index < field.size()) {
            					Field f = field.get(index);
        						String str = f.get_name();
        						if (columnName.equalsIgnoreCase(str)) {
        							removeFieldIndex.add(index);
        							break;
        						}							
            					index++;
            				}    				    	
            			}
                        for (int i = field.size() - 1; i >= 0; i--) {
                        	if (!removeFieldIndex.contains(new Integer(i)))
                        		field.remove(i);					
        				}
                        for (int i = 0; i < rows.size(); i++) {
        					Row r = rows.get(i);
        					List<Object> data = r.get_data();
        					for (int j = data.size() - 1; j >= 0; j--) {
        						if (!removeFieldIndex.contains(new Integer(j)))
        							data.remove(j);
        					}
        				}
                        
                        HashMap<Integer, List<Integer>> arr = Utilities.GetRowId(columnNames, rows, field);
                        List<Integer> remove = new ArrayList<Integer>();                        
                        for (Entry<Integer, List<Integer>> keyValue : arr.entrySet()) {
							List<Integer> data = keyValue.getValue();
							for (int i = 1; i < data.size(); i++) {
								remove.add(data.get(i));
							}
						}
                        for (int i = 0; i < remove.size() - 1; i++) {
							for (int j = i + 1; j < remove.size(); j++) {
								if (remove.get(i) < remove.get(j)) {
									Integer tmp = remove.get(i);
									remove.set(i, remove.get(j));
									remove.set(j, tmp);
								}
							}
						}
                        for (int i = 0; i < remove.size(); i++) {
                        	int index = remove.get(i);
							rows.remove(index);
						}
                	}
            		else {
            			if (sqd.getSelectQueryType() == SelectQueryType.NORMAL) {
            				List<Integer> removeFieldIndex = new ArrayList<Integer>();            				                                            				
                			String columnName = sqd.getColumnNames().get(0).trim();
                			if (!columnName.equalsIgnoreCase("*")) {
                				int index = 0;    				
                    			while (index < field.size()) {
                    				Field f = field.get(index);
                					String str = f.get_name();
                					if (columnName.equalsIgnoreCase(str)) {
                						removeFieldIndex.add(index);
                						break;
                					}							
                    				index++;
                    			}    				    	                			
                                for (int i = field.size() - 1; i >= 0; i--) {
                                	if (!removeFieldIndex.contains(new Integer(i)))
                                		field.remove(i);					
                				}
                                for (int i = 0; i < rows.size(); i++) {
                					Row r = rows.get(i);
                					List<Object> data = r.get_data();
                					for (int j = data.size() - 1; j >= 0; j--) {
                						if (!removeFieldIndex.contains(new Integer(j)))
                							data.remove(j);
                					}
                				}
                			}                			
            			}
            		}           			
            	}            		            	
            }
                
            GroupBy gb = new GroupBy();
            List<String> columnGroup = gb.ParseGroupBy(root);
            if (columnGroup != null) {
            	HashMap<Integer, List<Integer>> hm = Utilities.GetRowId(columnGroup, rows, field);
                for (int i = 0; i < sqds.size(); i++) {
    				SelectQueryData sqd = sqds.get(i);
    				if (sqd.getSelectQueryType() == SelectQueryType.MIN) {					
    					String columnName = sqd.getColumnNames().get(0);
    					int index = 0;
    					while (index < field.size()) {
    						if (field.get(index).get_name().equalsIgnoreCase(columnName)) 
    							break;						
    						index++;
    					}
    					field.add(new Field("Min(" + columnName + ")", "int"));					
    					for (Entry<Integer, List<Integer>> entries : hm.entrySet()) {
    						int idx = entries.getKey();
    						int minValue = new Integer(rows.get(idx).get_data().get(index).toString());
    						List<Integer> l = entries.getValue();
    						for (int j = 0; j < l.size(); j++) {
    							int m = l.get(j);
    							int value = new Integer(rows.get(m).get_data().get(index).toString());
    							if (minValue > value)
    								minValue = value;
    						}
    						rows.get(idx).get_data().add(minValue);	
    					}
    										
    					List<Row> r = new ArrayList<Row>();
    					for (Entry<Integer, List<Integer>> entry : hm.entrySet()) {
    						int idx = entry.getKey();
    						r.add(rows.get(idx));						
    					}
    					rows.clear();
    					for (int j = 0; j < r.size(); j++) {
    						rows.add(r.get(j));
    					}					
    				}
    				if (sqd.getSelectQueryType() == SelectQueryType.MAX) {
    					String columnName = sqd.getColumnNames().get(0);
    					int index = 0;
    					while (index < field.size()) {
    						if (field.get(index).get_name().equalsIgnoreCase(columnName)) 
    							break;						
    						index++;
    					}
    					field.add(new Field("Max(" + columnName + ")", "int"));					
    					for (Entry<Integer, List<Integer>> entries : hm.entrySet()) {
    						int idx = entries.getKey();
    						int maxValue = new Integer(rows.get(idx).get_data().get(index).toString());
    						List<Integer> l = entries.getValue();
    						for (int j = 0; j < l.size(); j++) {
    							int m = l.get(j);
    							int value = new Integer(rows.get(m).get_data().get(index).toString());
    							if (maxValue < value)
    								maxValue = value;
    						}
    						rows.get(idx).get_data().add(maxValue);	
    					}
    										
    					List<Row> r = new ArrayList<Row>();
    					for (Entry<Integer, List<Integer>> entry : hm.entrySet()) {
    						int idx = entry.getKey();
    						r.add(rows.get(idx));						
    					}
    					rows.clear();
    					for (int j = 0; j < r.size(); j++) {
    						rows.add(r.get(j));
    					}
    				}
    				if (sqd.getSelectQueryType() == SelectQueryType.AVG) {
    					String columnName = sqd.getColumnNames().get(0);
    					int index = 0;
    					while (index < field.size()) {
    						if (field.get(index).get_name().equalsIgnoreCase(columnName)) 
    							break;						
    						index++;
    					}
    					field.add(new Field("Avg(" + columnName + ")", "int"));					
    					for (Entry<Integer, List<Integer>> entries : hm.entrySet()) {
    						int idx = entries.getKey();
    						int avgValue = new Integer(rows.get(idx).get_data().get(index).toString());
    						List<Integer> l = entries.getValue();
    						for (int j = 1; j < l.size(); j++) {
    							int a = l.get(j);
    							int value = new Integer(rows.get(a).get_data().get(index).toString());
    							avgValue += value;
    						}
    						float avg = avgValue * 1.0f / l.size();
    						rows.get(idx).get_data().add(avg);	
    					}
    										
    					List<Row> r = new ArrayList<Row>();
    					for (Entry<Integer, List<Integer>> entry : hm.entrySet()) {
    						int idx = entry.getKey();
    						r.add(rows.get(idx));						
    					}
    					rows.clear();
    					for (int j = 0; j < r.size(); j++) {
    						rows.add(r.get(j));
    					}
    				}
    				if (sqd.getSelectQueryType() == SelectQueryType.SUM) {
    					String columnName = sqd.getColumnNames().get(0);
    					int index = 0;
    					while (index < field.size()) {
    						if (field.get(index).get_name().equalsIgnoreCase(columnName)) 
    							break;						
    						index++;
    					}
    					field.add(new Field("Sum(" + columnName + ")", "int"));					
    					for (Entry<Integer, List<Integer>> entries : hm.entrySet()) {
    						int idx = entries.getKey();
    						int sumValue = new Integer(rows.get(idx).get_data().get(index).toString());
    						List<Integer> l = entries.getValue();
    						for (int j = 1; j < l.size(); j++) {
    							int s = l.get(j);
    							int value = new Integer(rows.get(s).get_data().get(index).toString());
    							sumValue += value;
    						}						
    						rows.get(idx).get_data().add(sumValue);	
    					}
    										
    					List<Row> r = new ArrayList<Row>();
    					for (Entry<Integer, List<Integer>> entry : hm.entrySet()) {
    						int idx = entry.getKey();
    						r.add(rows.get(idx));						
    					}
    					rows.clear();
    					for (int j = 0; j < r.size(); j++) {
    						rows.add(r.get(j));
    					}
    				}
    				if (sqd.getSelectQueryType() == SelectQueryType.COUNT) {
    					String columnName = sqd.getColumnNames().get(0);
    					int index = 0;
    					while (index < field.size()) {
    						if (field.get(index).get_name().equalsIgnoreCase(columnName)) 
    							break;						
    						index++;
    					}
    					field.add(new Field("Count(" + columnName + ")", "int"));					
    					for (Entry<Integer, List<Integer>> entries : hm.entrySet()) {
    						int idx = entries.getKey();    						
    						int count = entries.getValue().size();    								
    						rows.get(idx).get_data().add(count);	
    					}
    										
    					List<Row> r = new ArrayList<Row>();
    					for (Entry<Integer, List<Integer>> entry : hm.entrySet()) {
    						int idx = entry.getKey();
    						r.add(rows.get(idx));						
    					}
    					rows.clear();
    					for (int j = 0; j < r.size(); j++) {
    						rows.add(r.get(j));
    					}
    				}
    			}
            }
                                	            
            ret.clear();
            for (int i = 0; i < rows.size(); i++) {
            	Row r = rows.get(i);
            	List<Object> data = r.get_data();
            	List<String> str = new ArrayList<>();
            	for (int j = 0; j < data.size(); j++) {
            		str.add(data.get(j).toString());
            	}
            	ret.add(str);
            }
            
            OrderBy orderBy = new OrderBy();
            List<OrderByType> orderByType = orderBy.ParseOrderBy(root);
            if (orderByType != null) {
            	for (int i = 0; i < orderByType.size(); i++) {
					OrderByType odt = orderByType.get(i);
					if (i > 0) {
						int index = 0;						
						Point p = searchDuplicateRow(ret, field, orderByType.get(i - 1), index);
						while (p != null) {
							ret = sortTable(ret, field, odt.getM_table() == null ? odt.getM_attribute() : odt.getM_table() + "." + odt.getM_attribute(), odt.getM_isDesc(), p);
							p = searchDuplicateRow(ret, field, orderByType.get(i - 1), p.y + 1);
						}						
					}
					else
						ret = sortTable(ret, field, odt.getM_table() == null ? odt.getM_attribute() : odt.getM_table() + "." + odt.getM_attribute(), odt.getM_isDesc(), new Point(0, ret.size() - 1));					
				}
            }
            
            List<String> header = new ArrayList<String>();
            for (int i = 0; i < field.size(); i++) {
				header.add(field.get(i).get_name());
			}
            ret.add(0, header);
            return ret;
		}
        catch (Exception e) {     
			return null;
		}                                      
	}
	
	private Point searchDuplicateRow(List<List<String>> ret, List<Field> field,
			OrderByType orderByType, int index) {
		int beginIndex = -1;
		int endIndex = -1;
		int idx = 0;
		while (idx < field.size()) {
			if (field.get(idx).get_name().equalsIgnoreCase(orderByType.getM_table() == null ? orderByType.getM_attribute() : orderByType.getM_table() + "." + orderByType.getM_attribute()))
				break;
			idx++;
		}
		for (int i = index; i < ret.size() - 1; i++) {
			List<String> r1 = ret.get(i);
			List<String> r2 = ret.get(i + 1);
			if (r1.get(idx).equals(r2.get(idx))) {
				if (beginIndex == -1) {
					beginIndex = i;
					endIndex = i + 1;
				}
				else {
					endIndex = i + 1;
				}
			}
			else {
				if (beginIndex != -1) 
				{
					endIndex = i;
					break;
				}
			}
		}
		if (beginIndex == -1)
			return null;
		return new Point(beginIndex, endIndex);
	}
	
	private List<List<String>> sortTable(List<List<String>> ret,
			List<Field> field, String m_attribute, Boolean m_isDesc, Point point) {
		int index = 0;
		while (index < field.size()) {
			if (field.get(index).get_name().equalsIgnoreCase(m_attribute))
				break;
			index++;
		}
		for (int i = point.x; i < point.y ; i++) {
			for (int j = i + 1; j <= point.y; j++) {
				List<String> r1 = ret.get(i);
				List<String> r2 = ret.get(j);
				if (m_isDesc) {
					if (field.get(index).get_dataType().equalsIgnoreCase("int")) {
						Integer value1 = new Integer(r1.get(index));
						Integer value2 = new Integer(r2.get(index));
						if (value1 < value2) {
							for (int k = 0; k < r1.size(); k++) {
								String tmp = r1.get(k);
								r1.set(k, r2.get(k));
								r2.set(k, tmp);
							}
						}
					}
					else {
						if (r1.get(index).compareTo(r2.get(index)) < 0) {
							for (int k = 0; k < r1.size(); k++) {
								String tmp = r1.get(k);
								r1.set(k, r2.get(k));
								r2.set(k, tmp);
							}
						}
					}					
				}
				else {
					if (field.get(index).get_dataType().equalsIgnoreCase("int")) {
						Integer value1 = new Integer(r1.get(index));
						Integer value2 = new Integer(r2.get(index));
						if (value1 > value2) {
							for (int k = 0; k < r1.size(); k++) {
								String tmp = r1.get(k);
								r1.set(k, r2.get(k));
								r2.set(k, tmp);
							}
						}
					}
					else {
						if (r1.get(index).compareTo(r2.get(index)) > 0) {
							for (int k = 0; k < r1.size(); k++) {
								String tmp = r1.get(k);
								r1.set(k, r2.get(k));
								r2.set(k, tmp);
							}
						}	
					}					
				}				
			}
		}
		
		return ret;
	}
	
	private String executeInsertQuery(String root, String databaseName) {
        try {
        	String query = root.toUpperCase();
            int beginIndex = 0;
            int endIndex = 0;
            String tableName = null;
            String value = null;
            endIndex = query.indexOf("INTO", beginIndex);
            if (endIndex < beginIndex) return null;
            beginIndex = endIndex + 4;
            endIndex = query.indexOf("VALUES", beginIndex);
            if (endIndex < beginIndex) return null;
            tableName = root.substring(beginIndex, endIndex);
            tableName = tableName.trim();
            beginIndex = endIndex + 6;
            value = root.substring(beginIndex);
            value = value.trim();
            ///////////////////////////////////////////////////////////////////
            endIndex = beginIndex = 0;
            endIndex = value.indexOf('(');
            if (endIndex < beginIndex) return null;
            beginIndex = endIndex + 1;
            endIndex = value.indexOf(')', beginIndex + 1);
            value = value.substring(beginIndex, endIndex);
            String[] values = value.split(",");
            for (int i = 0; i < values.length; i++) {
                values[i] = values[i].trim();
            }
            endIndex = beginIndex = 0;
            endIndex = tableName.indexOf('(', beginIndex);            
            if (endIndex == beginIndex) return null;
            ////////////////////////////////////////////////////////////////////////////////////////////////
            ////////////////////////////////////////////////////////////////////////////////////////////////
            if (endIndex < beginIndex) {//Khong co dau (), nghia la gan toan bo du lieu
                Database database = null;
                for (int i = 0; i < this._databases.size(); i++) {                    
                    if (this._databases.get(i).toString().toUpperCase().equals(databaseName.toUpperCase())) {                        
                        database = _databases.get(i);
                        break;
                    }                        
                }
                List<Table> v = database.get_tables();                        
                Table table = null;
                
                for (int i = 0; i < v.size(); i++) {
                    table = v.get(i);
                    if (table.toString().toUpperCase().equals(tableName.toUpperCase()))
                        break;
                    table = null;
                }
                if (table == null) return null;
                if (table.get_fields().size() != values.length) return null;
                ArrayList<Object> list = new ArrayList<Object>();
                String type = null;
                for (int i = 0; i < values.length; i++) {                                        
                    type = table.get_fields().get(i).get_dataType();
                    if (type.equals("String")) {
                        values[i] = values[i].replace('\'', ' ').trim();
                        list.add(new String(values[i]));
                    }
                    else {                                            
                        if (type.equals("int")) {
                            int Int = Integer.parseInt(values[i]);
                            list.add(Integer.parseInt(values[i]));
                        }
                    }                        
                }
                System.out.println("Add new row");
                
                Row r = new Row(list);
                table.get_rows().add(r);
                saveDatabase(databaseName);
            }
            ////////////////////////////////////////////////////////////////////////////////////////////////
            ////////////////////////////////////////////////////////////////////////////////////////////////
            else {//Co dau ngoac, tuc la gan cho 1 vai cot co dinh
                Database database = null;
                for (int i = 0; i < this._databases.size(); i++) {                    
                    if (this._databases.get(i).toString().toUpperCase().equals(databaseName.toUpperCase())) {                        
                        database = this._databases.get(i);
                        break;
                    }                        
                }
                beginIndex = 0;
                endIndex = 0;
                endIndex = tableName.indexOf('(');
                if (endIndex <= beginIndex) return null;
                String tmp = tableName.substring(endIndex + 1);
                tableName = tableName.substring(beginIndex, endIndex).trim();
                endIndex = tmp.indexOf(')');
                if (endIndex <= beginIndex) return null;
                tmp = tmp.substring(0, endIndex).trim();                
                String[]columnsName = tmp.split(",");
                if (columnsName.length != values.length) return null;                
                for (int i = 0; i < columnsName.length; i++) {
                    columnsName[i] = columnsName[i].trim();
                }
                List<Table> v = database.get_tables();                        
                Table table = null;
                for (int i = 0; i < v.size(); i++) {
                    table = v.get(i);
                    if (table.toString().toUpperCase().equals(tableName.toUpperCase()))
                        break;
                    table = null;
                }
                if (table == null) return null;
                //Xac dinh cac cot                                
                ArrayList<Object> list = new ArrayList<Object>();
                String type = null;
                String fieldName = null;
                for (int i = 0; i < table.get_fields().size(); i++) {                                        
                    type = table.get_fields().get(i).get_dataType();
                    fieldName = table.get_fields().get(i).get_name();
                    int j = 0;
                    boolean flag = false;
                    while (j < columnsName.length) {
                        if (columnsName[j].toUpperCase().equals(fieldName.toUpperCase().trim())) {
                            if (type.equals("String")) {
                                values[j] = values[j].replace('\'', ' ').trim();
                                list.add(new String(values[j]));
                            }
                            else {                                            
                                if (type.equals("int")) {
                                    int Int = Integer.parseInt(values[j]);
                                    list.add(Int);
                                }
                            }            
                            flag = true;
                            break;
                        }
                        j++;
                    }
                    if (!flag) {
                    	if (type.equals("String")) {
                    		list.add(new String());
                    	}
                    	if (type.equals("int")) {
                    		list.add(0);
                    	}
                    }
                                            
                }
                Row r = new Row(list);
//                System.out.println(String.format("Added new row (%s, %s, %s, %s)", r.m_list.get(0).toString(), r.m_list.get(1).toString(), r.m_list.get(2).toString(), r.m_list.get(3).toString()));
                table.get_rows().add(r);
                saveDatabase(databaseName);
            }                
        }
        catch (Exception e) {
            
        }        
        return null;
    }
	
	private String executeDeleteQuery(String root, String database) {
        try {
        	String query = root.toUpperCase();
            int beginIndex = 0;
            int endIndex = 0;
            String star = null;
            String tableName = null;
            String where = null;
            endIndex = query.indexOf("DELETE", beginIndex);
            if (endIndex < beginIndex) return null;
            beginIndex = endIndex + 6;
            endIndex = query.indexOf("FROM", beginIndex);
            if (endIndex < beginIndex) return null;
            star = root.substring(beginIndex, endIndex);
            star = star.trim();
            beginIndex = endIndex + 4;
            endIndex = query.indexOf("WHERE", beginIndex);
            if (endIndex < beginIndex) { //Khong co where, xoa toan bo
            	if (star.equalsIgnoreCase("") || star.equals("*")) {
            		tableName = root.substring(beginIndex);
                	tableName = tableName.trim();
                	Table t = getTable(database, tableName);
                	t.get_rows().clear();
                	saveDatabase(database);
                	return null;
            	}          
            	return null;
            }
            
            tableName = root.substring(beginIndex, endIndex);
            tableName = tableName.trim();
            where = root.substring(endIndex);            
            
            Table t = getTable(database, tableName);
            List<Field> fields = t.get_fields();            
            List<Row> rows = t.get_rows();
            int[] flags = new int[rows.size()];
            int idx = 0;
            for (int i = 0; i < rows.size(); i++) {				
				boolean check = ParseWhere.QueryParse(fields, rows.get(i), where);
				if (check) 
					flags[idx++] = i;				
			}
            for (int i = idx-1; i >= 0; i--) {
				rows.remove(flags[i]);
			}
            saveDatabase(database);
        }
        catch (Exception e) {
            return "Fail";
        }        
        return null;
    }

    private static String getUpdate(String root)
    {
        String query = root.toUpperCase();
        int beginIndex = 0;
        int endIndex = 0;
        endIndex = query.indexOf("UPDATE", beginIndex);
        if (endIndex < beginIndex) return null;
        beginIndex = endIndex + 6;
        endIndex = query.indexOf("SET", beginIndex);
        if (endIndex < beginIndex)
            return null;
        return root.substring(beginIndex, endIndex).trim();
    }
    
    private static String getSet(String root)
    {
    	String query = root.toUpperCase();
        int beginIndex = 0;
        int endIndex = 0;
        endIndex = query.indexOf("UPDATE", beginIndex);
        if (endIndex < beginIndex) return null;
        beginIndex = endIndex + 6;
        endIndex = query.indexOf("SET", beginIndex);
        if (endIndex < beginIndex)
            return null;
        beginIndex = endIndex + 3;
        endIndex = query.indexOf("WHERE", beginIndex);
        if (endIndex < beginIndex)
            return root.substring(beginIndex).trim();
        return root.substring(beginIndex, endIndex).trim();
    }
    
    private static String getWhere(String root)
    {
    	String query = root.toUpperCase();
        int beginIndex = 0;
        int endIndex = 0;
        endIndex = query.indexOf("UPDATE", beginIndex);
        if (endIndex < beginIndex) return null;
        beginIndex = endIndex + 6;
        endIndex = query.indexOf("SET", beginIndex);
        if (endIndex < beginIndex)
            return null;
        beginIndex = endIndex + 3;
        endIndex = query.indexOf("WHERE", beginIndex);
        if (endIndex < beginIndex)
            return null;
        return root.substring(endIndex).trim();
        
    }
    
    private static Boolean QueryIsValid(String query)
    {
        int endIndex = query.indexOf(">=");
        if (endIndex >= 0)
            return false;
        endIndex = query.indexOf("<=");
        if (endIndex >= 0)
            return false;
        endIndex = query.indexOf("<");
        if (endIndex >= 0)
            return false;
        endIndex = query.indexOf(">");
        if (endIndex >= 0)
            return false;
        
        if (query.indexOf("==") >= 0)
            return false;
        
        
        if (query.indexOf("=>") >= 0)
            return false;
        if (query.indexOf("=<") >= 0)
            return false;        
        return true;
        
    }
    
    private static ExpressionNode String2ExpressionNode(String str)
    {
        
        int beginIndex = 0;

        if (QueryIsValid(str) == false)
            return null;
        
        int endIndex = str.indexOf("=", beginIndex);
        if (endIndex < beginIndex)
            return null;
        String attribute = str.substring(beginIndex, endIndex).trim();
        beginIndex = endIndex + 1;
        String value = str.substring(beginIndex).trim();
        
        
        return new ExpressionNode(attribute, "=", value);
        
    }
    
    private static List<ExpressionNode> getOperands(String query)
    {
        ArrayList<String> operands = new ArrayList<String>(); 
        String REGEX = ",";
        Pattern p = Pattern.compile(REGEX);
        String[] items = p.split(query);
        for (int i = 0 ; i < items.length ; i++)
            operands.add(items[i].trim());
        
        
        ArrayList<ExpressionNode> expNodeArr = new ArrayList<ExpressionNode>();
        for (int i = 0 ; i < operands.size() ; i++)
            expNodeArr.add(String2ExpressionNode(operands.get(i)));
        return expNodeArr;
        
    }
    
    private static Boolean setQueryIsValid(ArrayList<ExpressionNode> list)
    {
        for (int i = 0 ; i < list.size() ; i++)
            if (list.get(i) == null)
                return false;
        return true;
    }
    
    private String executeUpdateQuery(String query, String database) 
    {	
        // Xu ly table
        String tableName = getUpdate(query); // Lay ten bang
        if (tableName == null) // cau lenh khong hop le
            return null;
        Table t = getTable(database, tableName);
        if (t == null)
        	return null;
        
        // Xu ly doan SET gia tri
        ArrayList<ExpressionNode> en = (ArrayList<ExpressionNode>) getOperands(getSet(query));

        if (setQueryIsValid(en) == false)
            return null;
        List<Field> fields = t.get_fields();
        List<Row> rows = t.get_rows();        
        String whereString = getWhere(query);              
        for (int i = 0; i < rows.size(); i++) {
        	Row r = rows.get(i);
        	boolean check;
        	if (whereString == null)
        		check = true;
        	else
        		check = ParseWhere.QueryParse(fields, r, whereString);
			if (check) {
				for (int j = 0; j < en.size(); j++) {
					ExpressionNode node = en.get(j);
					String columnName = node.getM_attribute();
					int index = 0;
					while (index < fields.size()) {						
						if (fields.get(index).get_name().equalsIgnoreCase(columnName))
							break;
						index++;						
					}
					if (index < fields.size()) {
						List<Object> data = r.get_data();
						String value = node.getM_value().toString();
						if (fields.get(index).get_dataType().equalsIgnoreCase("string")) {
							value = value.substring(1);
							value = value.substring(0, value.length() - 1);
							data.set(index, value);
						}
						else
							if (fields.get(index).get_dataType().equalsIgnoreCase("int")) {
								data.set(index, new Integer(value));
							}
					}
				}
			}
		}        
        saveDatabase(database);
        return null;
    }

	public void deleteDatabase(String database, String tableName) {
		Database db = this.getDatabase(database);
		if (db != null) {
			db.removeTable(tableName);
		}	
	}
}
